
package com.feib.soeasy.dao.hibernate;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.PaymentDao;
import com.feib.soeasy.model.FormatDefinition;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.PaymentCanceled;
import com.feib.soeasy.model.UploadFile;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)PaymentHibernateDao.java<br>
 * @description 繳費單 Dao 實作<br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/11/18 create by Jimmy Liu<br>
 */
@Repository("paymentDao")
public class PaymentHibernateDao extends BaseHibernateDao<Payment, Long> implements PaymentDao {


	public List<Long> queryPaymentPkByUploadFilePk(Long uploadFilePk)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("select payment.paymentPk from Payment payment ");
		hql.append("where payment.uploadFile = ");
		hql.append(String.valueOf(uploadFilePk));

		List<Long> list = getSession().createQuery(hql.toString()).list();

		return list;
	}



	public List sumTotalPaymentByGroup(String channel ,Date qryDateFrom, Date qryDateTo , String commissionCode, List commissionCodes )
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct count(p.payment_pk) as count  ,(select g.group_name from app_group g where p.group_pk = g.group_pk ) as group_name,  ");
		sql.append("( case when c.commission_type ='O' then sum(p.total_amount - c.group_amount) else sum(p.total_amount) end ) as real_pay_amount,  ");
		sql.append(" sum(p.total_amount) as total_amount , p.group_pk ");
		sql.append("from payment p , write_off_payment w, commission_code_table c ");
		sql.append("where p.payment_pk = w.payment_pk ");
		sql.append("and  w.write_off_date between  :qryDateFrom and :qryDateTo ");
		sql.append("and p.payment_status = 5 ");
		sql.append("and w.channel = :channel ");
		sql.append("and (case when p.total_amount =w.amount then substring(p.barcode_stores_1 , 7, 3) else substring(p.barcode_stores_1_loan,7,3 ) end ) = c.commission_code ");

		if(commissionCodes != null)
			sql.append("and c.commission_code in  :commissionCodes ");
		else
			sql.append("and c.commission_code =  :commissionCode ");

		sql.append("and c.commission_code_pk in ");
		sql.append("(");
		sql.append("SELECT FDC.commission_code_pk FROM FORMAT_DEF_COMMISSION FDC WHERE FDC.FORMAT_DEF_PK IN ( ");
		sql.append("  SELECT F.FORMAT_DEF_PK FROM UPLOAD_FILE F where f.upload_file_pk = p.upload_file_pk ) ");
		sql.append(") ");
		sql.append("group by p.group_pk,  c.commission_type ");



		Query query = this.getSession().createSQLQuery(sql.toString());
		query.setParameter("qryDateFrom", qryDateFrom);
		query.setParameter("qryDateTo", qryDateTo);
		query.setParameter("channel", channel);
		if(commissionCodes != null)
			query.setParameterList("commissionCodes", commissionCodes);
		else	
			query.setParameter("commissionCode", commissionCode);


		return query.list();
	}



	//2011.12 by Leo
	public List<Payment> queryPaymentByParam(Payment payment, String userno, String type)
	{

		StringBuffer hql = new StringBuffer();
		hql.append("from Payment payment ");
		hql.append("where payment.group = :group ");
		hql.append(" and payment.uploadFile.formatDefinition = :formatDefinition ");
		if(type.equals("1"))
			hql.append(" and payment.virtualAccount = :userno ");
		else
			hql.append(" and substring(payment.virtualAccount,1,14) = :userno ");


		if(payment.getPayerName() != null && !"".equals(payment.getPayerName()))
		{
			hql.append(" and payment.payerName like :payerName ");
		}

		if(payment.getVirtualAccount() != null && !"".equals(payment.getVirtualAccount()))
		{
			hql.append(" and payment.virtualAccount like :virtualAccount ");
		}
		if(payment.getPayerId() != null && !"".equals(payment.getPayerId()))
		{
			hql.append(" and payment.payerId like :payerId ");
		}

		hql.append("order by payment.virtualAccount ");

		Query query = getSession().createQuery(hql.toString());
		query.setParameter("group", payment.getGroup());
		query.setParameter("formatDefinition", payment.getUploadFile().getFormatDefinition());
		query.setParameter("userno", userno);

		if(payment.getPayerName() != null && !"".equals(payment.getPayerName()))
		{
			query.setParameter("payerName", "%" + payment.getPayerName() + "%");
		}

		if(payment.getVirtualAccount() != null && !"".equals(payment.getVirtualAccount()))
		{
			query.setParameter("virtualAccount", "%" + payment.getVirtualAccount() + "%");
		}

		if(payment.getPayerId() != null && !"".equals(payment.getPayerId()))
		{
			query.setParameter("payerId", "%" + payment.getPayerId() + "%");
		}

		return query.list();
	}

	//2011.12 by Leo
	public List<Payment> queryPaymentByGroup(Group group, Integer qryDateType, Date qryDateFrom, Date qryDateTo)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("from Payment payment ");
		hql.append("where payment.group = :group ");

		hql.append(" and payment.paymentStatus = 5 ");
		hql.append(" and payment.paymentPk in ( ");
		hql.append(" select w.payment from WriteOffPayment w ");

		// 資料期間 1: 銷帳日期  2:實際繳款日
		if (new Integer(1).equals(qryDateType))
			hql.append(" where w.writeOffDate between :qryDateFrom and :qryDateTo "); // 銷帳日期
		else
			hql.append(" where w.txDate between :qryDateFrom and :qryDateTo "); // 實際繳款日

		hql.append(" ) ");

		Query query = getSession().createQuery(hql.toString());
		query.setParameter("group", group);
		query.setParameter("qryDateFrom", qryDateFrom);
		query.setParameter("qryDateTo", qryDateTo);

		return query.list();
	}

	/**
	 * 依uploadfile pk 值來刪除繳費資料
	 * @param uploadFile
	 */
	public void deletePaymentByUploadFile(UploadFile uploadFile)
	{

		StringBuffer hql = new StringBuffer();
		hql.append("from Payment as p where p.uploadFile =:uploadFile");
		Query querypk = getSession().createQuery(hql.toString());       
		querypk.setParameter("uploadFile", uploadFile);
		List <Payment> list = querypk.list();

		List <Long> paymentPks = new ArrayList();        
		for (java.util.Iterator i=list.iterator();i.hasNext();)
		{
			Payment payment = (Payment)i.next();
			Long paymentPK = payment.getPaymentPk();
			paymentPks.add(paymentPK);
		}        

		List <Long> extraColumnsPks = new ArrayList();
		for (java.util.Iterator i=list.iterator();i.hasNext();)
		{
			Payment payment = (Payment)i.next();
			if (payment.getExtraColumns() != null){
				Long extraColumnsPk = payment.getExtraColumns().getExtraColumnsPk();
				extraColumnsPks.add(extraColumnsPk);
			}
		}

		int records = list.size();
		int recordPages = 100;
		int batch = records/recordPages + 1;
		for(int i=0;i<batch;i++)
		{
			List deletePks = new ArrayList();
			for(int j=i*recordPages;j<(1+i)*recordPages;j++)
			{
				if(j == records)
				{
					break;
				}
				deletePks.add(paymentPks.get(j));
			}
			if (deletePks.size() > 0)
			{
				hql = new StringBuffer();
				hql.append("delete Payment as p ");
				hql.append("where p.paymentPk in (:paymentPks) ");        
				Query query = getSession().createQuery(hql.toString());
				query.setParameterList("paymentPks", deletePks);        
				query.executeUpdate();
			}    

		}

		if (extraColumnsPks.size() != 0){
			for(int i=0;i<batch;i++)
			{
				List deletePks = new ArrayList();
				for(int j=i*recordPages;j<(1+i)*recordPages;j++)
				{
					if(j == records)
					{
						break;
					}
					deletePks.add(extraColumnsPks.get(j));
				}
				if (deletePks.size() > 0)
				{
					hql = new StringBuffer();
					hql.append("delete ExtraColumns as p ");
					hql.append("where p.extraColumnsPk in (:extraColumnsPks) ");        
					Query extraQuery = getSession().createQuery(hql.toString());
					extraQuery.setParameterList("extraColumnsPks", deletePks); 
					extraQuery.executeUpdate();
				}
			}
		}
		/*
        hql = new StringBuffer();
        hql.append("delete Payment as p ");
        hql.append("where p.paymentPk in (:paymentPks) ");        
        Query query = getSession().createQuery(hql.toString());
        query.setParameterList("paymentPks", paymentPks);        
        query.executeUpdate();

        hql = new StringBuffer();
        hql.append("delete ExtraColumns as p ");
        hql.append("where p.extraColumnsPk in (:extraColumnsPks) ");        
        Query extraQuery = getSession().createQuery(hql.toString());
        extraQuery.setParameterList("extraColumnsPks", extraColumnsPks); 
        extraQuery.executeUpdate();
		 */
	}

	/**
	 * 依uploadfile pk 值整批更新繳費狀態
	 * @param paymentStatus
	 * @param uploadFile
	 */
	public void updatePaymentStatusByUploadFilePk(Integer paymentStatus , UploadFile uploadFile){

		StringBuffer hql = new StringBuffer();
		hql.append("update Payment set paymentStatus = :paymentStatus ");
		hql.append("where uploadFile = :uploadFile ");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter("paymentStatus", paymentStatus);
		query.setParameter("uploadFile", uploadFile);
		query.executeUpdate();

	}

	/**
	 * 取得檔案應繳總金額
	 * @param uploadFilePk	上傳檔案檔pk 值
	 * @param paymentStatus 繳費狀態
	 * @return 總金額
	 * @author Leo Lee
	 */
	public Long getTotalAmountByUploadFilePk(Long uploadFilePk , Integer paymentStatus)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("select sum(payment.totalAmount) from Payment payment ");
		hql.append("where payment.uploadFile = ");
		hql.append(String.valueOf(uploadFilePk));

		if(null != paymentStatus)
		{
			hql.append("and payment.paymentStatus = ");
			hql.append(String.valueOf(paymentStatus));
		}

		List list = getSession().createQuery(hql.toString()).list();

		if(null == list || list.isEmpty())
		{
			return new Long(0);
		}


		return (Long)list.get(0);

	}

	/**
	 * 取得檔案應該總筆數
	 * @param uploadFilePk 上傳檔案檔pk 值
	 * @param paymentStatus 繳費狀態
	 * @return 總筆數, 若回傳為 0 則查無應繳筆數
	 * @author Leo Lee
	 */
	public Long getCountItemByUploadFilePk(Long uploadFilePk , Integer paymentStatus)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("select count(payment.paymentPk) from Payment payment ");
		hql.append("where payment.uploadFile.uploadFilePk = ");
		hql.append(String.valueOf(uploadFilePk));

		if(null != paymentStatus)
		{
			hql.append("and payment.paymentStatus = ");
			hql.append(String.valueOf(paymentStatus));
		}

		List list = getSession().createQuery(hql.toString()).list();

		if(null == list || list.isEmpty())
		{
			return new Long(0);
		}


		return (Long)list.get(0);
	}

	/**
	 * 依據查詢物件與繳費截止日期起迄查詢繳費單
	 * @param payment 查詢條件
	 * @param payDueFrom 繳費截止日期起
	 * @param payDueTo 繳費截止日期迄
	 * @param paginationConfig 換頁條件
	 * @return 查詢結果物件
	 */
	@SuppressWarnings("unchecked")
	@Override
	public PaginatedList<Payment> queryPaginationByPayment(
			Payment payment, Date payDueFrom, Date payDueTo, PaginatedList<Payment> paginationConfig) {

		return queryPaginationByPayment(payment, payDueFrom, payDueTo, paginationConfig, null);
	}



	/**
	 * 依據查詢物件與繳費截止日期起迄查詢繳費單
	 * @param payment 查詢條件
	 * @param payDueFrom 繳費截止日期起
	 * @param payDueTo 繳費截止日期迄
	 * @param paginationConfig 換頁條件
	 * @param queryType 以不同的代號來加查詢條件 P: 下載查詢
	 * @return 查詢結果物件
	 */
	@SuppressWarnings("unchecked")
	@Override
	public PaginatedList<Payment> queryPaginationByPayment(
			Payment payment, Date payDueFrom, Date payDueTo, 
			PaginatedList<Payment> paginationConfig,
			String queryType) {
		PaginatedList<Payment> result = new PaginatedList<Payment>();

		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		result.setFullListSize(super.countByDetachedCriteria(
				createDetachedCriteriaByUploadFile(payment, payDueFrom, payDueTo, queryType)));


		// 筆數大於0
		if (0 < result.getFullListSize())
		{
			DetachedCriteria criteria = 
				createDetachedCriteriaByUploadFile(payment, payDueFrom, payDueTo, queryType);

			//排序
			if (null != paginationConfig.getSortCriterion())
			{
				// 由小到大
				if (PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection())
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
				else // 由大到小
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
			}


			int maxResults = result.getPageSize(); //the maximum number of results
			int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			result.setPaginatedList(this.getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults));
		}

		return result;
	}


	/**
	 * 產生繳費單查詢物件
	 * @param payment 查詢條件
	 * @param payDueFrom 繳費截止日期起
	 * @param payDueTo 繳費截止日期迄
	 * @param queryType 以不同的代號來加查詢條件 P: 下載查詢
	 * @return 查詢物件
	 */
	protected DetachedCriteria createDetachedCriteriaByUploadFile(
			Payment entity, Date payDueFrom, Date payDueTo, String queryType)
	{
		DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
		criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

		Example example = Example.create(entity);
		example.enableLike(MatchMode.START);
		example.excludeZeroes();
		criteria.add(example);

		// 屬於哪一個檔案
		if (null != entity.getUploadFile())
		{
			DetachedCriteria detachedCriteria = criteria.createCriteria("uploadFile");

			// PK 有值，需加入為查詢條件
			if (null != entity.getUploadFile().getUploadFilePk())
			{
				detachedCriteria.add(Restrictions.idEq(entity.getUploadFile().getUploadFilePk()));
			}

			// UploadFile 其他查詢條件
			Example e = Example.create(entity.getUploadFile());
			e.enableLike(MatchMode.START);
			e.excludeZeroes();
			detachedCriteria.add(e);
		}

		// 屬於哪一個群組
		if (null != entity.getGroup())
		{
			DetachedCriteria detachedCriteria = criteria.createCriteria("group");

			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk())
			{
				detachedCriteria.add(Restrictions.idEq(entity.getGroup().getGroupPk()));
			}

			// Group 其他查詢條件
			Example e = Example.create(entity.getGroup());
			e.enableLike(MatchMode.START);
			e.excludeZeroes();
			detachedCriteria.add(e);
		}

		// 繳費截止日期
		// 有起迄
		if (null != payDueFrom && null != payDueTo)
		{
			criteria.add(Restrictions.between("payDue",payDueFrom, payDueTo));
		}
		else if (null != payDueFrom) // 只有起
		{
			criteria.add(Restrictions.ge("payDue", payDueFrom));
		}
		else if (null != payDueTo) // 只有迄
		{
			criteria.add(Restrictions.le("payDue", payDueTo));
		}
		else if (null != entity.getPayDue()) //單一日期
		{
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			criteria.add(Restrictions.between("payDue", from, to));
		}

		//繳費狀態
		if(null != entity.getPaymentStatus())
		{
			//待繳交
			if(entity.getPaymentStatus().equals(Payment.PAYMENT_STATUS_IN_PAYING))
			{
				criteria.add(Restrictions.eq("paymentStatus", entity.getPaymentStatus()));
			}else
			{
				criteria.add(Restrictions.eq("paymentStatus", entity.getPaymentStatus()));
			}


		}
		//        //學校統一編號
		//        if(null != entity && null != entity.getGroup() && null != entity.getGroup().getGroupNo())
		//        {
		//            criteria.add(Restrictions.eq("group", entity.getGroup()));
		//        }
		//學號
		if(null != entity.getStudentId())
		{
			criteria.add(Restrictions.eq("studentId", entity.getStudentId()));
		}

		//判斷paymentCanceled是否有對應的資料
		if(null != entity.getPaymentCanceled())
		{
			if(null != entity.getPaymentCanceled().getPaymentCanceledPk())
			{
				//以PaymentCanceled查詢
				criteria.add(Restrictions.eq("paymentCanceled",	entity.getPaymentCanceled()));
			}
			else
			{
				criteria.add(Restrictions.isNull("paymentCanceled"));
			}
		}

		//判斷writeOffPayment是否有對應的資料
		if(null != entity.getWriteOffPayment())
		{
			if(null != entity.getWriteOffPayment().getWriteOffPaymentPk())
			{
				//以writeOffPayment查詢
				criteria.add(Restrictions.eq("writeOffPayment",	entity.getWriteOffPayment()));
			}
			else
			{
				criteria.add(Restrictions.isNull("writeOffPayment"));
			}
		}



		if(null != queryType)
		{
			//paymentStatus = 2(待繳費) , 3(註銷中)
			if("P".equals(queryType))
			{
				List<Integer> list = new ArrayList<Integer>();
				list.add(new Integer(2));
				list.add(new Integer(3));
				criteria.add(Restrictions.in("paymentStatus", list));
				criteria.add(Restrictions.isNull("writeOffPayment"));
			}

			//進度查詢, 除了放行中都要查到
			if("Q".equals(queryType))
			{
				criteria.add(Restrictions.ne("paymentStatus", new Integer(1)));
			}
		}

		return criteria;
	}

	public Payment getPaymentByPk(Payment payment)
	{
		Criteria criteria = getSession().createCriteria(Payment.class);
		criteria.add(Restrictions.eq("paymentPk",payment.getPaymentPk()));
		return (Payment)criteria.uniqueResult();
	}

	public List<Payment> getPaymentByPk(List<Payment> payment)
	{
		Criteria criteria = getSession().createCriteria(Payment.class);
		List<Long> paymentPk = new ArrayList<Long>();
		for(int i = 0 ; i < payment.size() ; i++){
			paymentPk.add(payment.get(i).getPaymentPk());
		}
		criteria.add(Restrictions.in("paymentPk",paymentPk));
		return (List<Payment>)criteria.list();
	}

	/**
	 * 依paymentStatus 取回 Payment 檔
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Payment> queryStatus3(Payment payment, PaginatedList<Payment> paginationConfig) {
		PaginatedList<Payment> result = new PaginatedList<Payment>();

		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		result.setFullListSize(super.countByDetachedCriteria(createDetachedCriteriaByPaymentStatus(payment)));


		// 筆數大於0
		if (0 < result.getFullListSize())
		{
			DetachedCriteria criteria = createDetachedCriteriaByPaymentStatus(payment);

			//排序
			if (null != paginationConfig.getSortCriterion())
			{
				// 由小到大
				if (PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection())
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
				else // 由大到小
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
			}

			int maxResults = result.getPageSize(); //the maximum number of results
			int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			result.setPaginatedList(this.getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults));
		}

		List<Payment> recordsList = result.getPaginatedList();
		return recordsList;
	}
	protected DetachedCriteria createDetachedCriteriaByPaymentStatus(Payment payment)
	{
		DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
		criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
		criteria.add(Restrictions.eq("paymentStatus", payment.getPaymentStatus()));
		criteria.createCriteria("paymentCanceled").add(Restrictions.isNull("confirmStatus"));
		//相同群組才查的到
		//如果不是銀行端，則查詢條件要加入帳單業者限制
		if (null != payment.getGroup())
		{
			criteria.createCriteria("group").add(Restrictions.idEq(payment.getGroup().getGroupPk()));
		}
		else if (!UserUtil.isBankGroup())
		{
			criteria.createCriteria("group").add(Restrictions.idEq(UserUtil.getUserGroup().getGroupPk()));
		}

		return criteria;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Payment> queryStatus2(Payment payment, PaginatedList<Payment> paginationConfig) {
		PaginatedList<Payment> result = new PaginatedList<Payment>();

		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		result.setFullListSize(super.countByDetachedCriteria(createDetachedCriteriaByPaymentStatus2(payment)));


		// 筆數大於0
		if (0 < result.getFullListSize())
		{
			DetachedCriteria criteria = createDetachedCriteriaByPaymentStatus2(payment);

			//排序
			if (null != paginationConfig.getSortCriterion())
			{
				// 由小到大
				if (PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection())
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
				else // 由大到小
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
			}

			int maxResults = result.getPageSize(); //the maximum number of results
			int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			result.setPaginatedList(this.getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults));
		}

		List<Payment> recordsList = result.getPaginatedList();
		return recordsList;
	}
	protected DetachedCriteria createDetachedCriteriaByPaymentStatus2(Payment payment)
	{
		DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
		criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
		criteria.add(Restrictions.eq("paymentStatus", payment.getPaymentStatus()));
		criteria.createCriteria("writeOffPayment").add(Restrictions.eq("writeOffStatus", new Integer(3)));
		//相同群組才查的到
		//如果不是銀行端，則查詢條件要加入帳單業者限制
		if (null != payment.getGroup())
		{
			criteria.createCriteria("group").add(Restrictions.idEq(payment.getGroup().getGroupPk()));
		}
		else if (!UserUtil.isBankGroup())
		{
			criteria.createCriteria("group").add(Restrictions.idEq(UserUtil.getUserGroup().getGroupPk()));
		}

		return criteria;
	}
	public void updatePaymentStatusAs2(ArrayList<Payment> payment){
		StringBuffer hql = new StringBuffer();
		hql.append("update Payment set paymentStatus = :paymentStatus, paymentCanceled = null  ");
		hql.append("where paymentPk = :paymentPk ");
		for(Payment p:payment){
			//20110721 忽略未勾選項目
			if (null == p || null == p.getPaymentPk())
				continue;

			Query query = getSession().createQuery(hql.toString());
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);
			query.setParameter("paymentPk", p.getPaymentPk());
			query.executeUpdate();
		}
	}

	public void updatePaymentStatusAs4(ArrayList<Payment> payment){
		StringBuffer hql = new StringBuffer();
		hql.append("update Payment set paymentStatus = :paymentStatus  ");
		hql.append("where paymentPk = :paymentPk ");
		for(Payment p:payment){
			//20110721 忽略未勾選項目
			if (null == p || null == p.getPaymentPk())
				continue;


			Query query = getSession().createQuery(hql.toString());
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CANCELED);
			query.setParameter("paymentPk", p.getPaymentPk());
			query.executeUpdate();
		}
	}

	public void updateWriteOffPaymentStatusAs2(ArrayList<Payment> payment){
		StringBuffer hql = new StringBuffer();
		hql.append("update Payment set writeOffPayment = null, realPayAmount = null ");
		hql.append("where paymentPk = :paymentPk ");
		for(Payment p:payment){
			//20110721 忽略未勾選項目
			if (null == p || null == p.getPaymentPk())
				continue; 

			Query query = getSession().createQuery(hql.toString());
			query.setParameter("paymentPk", p.getPaymentPk());
			query.executeUpdate();
		}
	}
	public void updateWriteOffPaymentStatusAs5(ArrayList<Payment> payment){
		StringBuffer hql = new StringBuffer();
		hql.append("update Payment set paymentStatus = :paymentStatus  ");
		hql.append("where paymentPk = :paymentPk ");
		for(Payment p:payment){
			//20110721 忽略未勾選項目
			if (null == p || null == p.getPaymentPk())
				continue;

			Query query = getSession().createQuery(hql.toString());
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CLOSED);
			query.setParameter("paymentPk", p.getPaymentPk());
			query.executeUpdate();
		}
	}

	public void updateWriteOffPaymentStatusAs5ForOnLine(WriteOffPayment writeOffPayment)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("update Payment set paymentStatus = :paymentStatus, writeOffPayment = :writeOffPayment, realPayAmount = :realPayAmount ");
		hql.append("where paymentPk = :paymentPk ");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CLOSED);
		query.setParameter("writeOffPayment", writeOffPayment);
		/* 20120119 YD Fix */
		query.setParameter("realPayAmount", writeOffPayment.getAmount());
		query.setParameter("paymentPk", writeOffPayment.getPayment().getPaymentPk());
		query.executeUpdate();
	}


	/**
	 * 2012 0929 0700
	 * TODO JEFF
	 */
	@SuppressWarnings("unchecked")
	public List<Payment> queryDownloadPayments(Group qryGroup,
			Date qryPayDueFrom, Date qryPayDueTo,
			String qryVirtualAccountFrom, String qryVirtualAccountTo) {
		Criteria criteria = getSession().createCriteria(Payment.class);
		criteria.add(Restrictions.eq("group", qryGroup));
		criteria.add(Restrictions.between("payDue", qryPayDueFrom, qryPayDueTo));
		criteria.add(Restrictions.between("virtualAccount", qryVirtualAccountFrom, qryVirtualAccountTo));
		return criteria.list();
	}

	@SuppressWarnings("unchecked")
	public List<Payment> queryByUploadFiles(
			UploadFile uploadFile, Integer qryPaymentStatus)
			{
		Criteria criteria = getSession().createCriteria(Payment.class);
		criteria.add(Restrictions.eq("uploadFile", uploadFile));
		if(qryPaymentStatus != null)
			criteria.add(Restrictions.eq("paymentStatus", qryPaymentStatus));

		return criteria.list();
			}

	public int countByFormatDefinitionPk(Long formatDefinitionPk)
	{
		DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
		criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
		criteria.setProjection(Projections.rowCount());
		DetachedCriteria uploadFileCriteria = criteria.createCriteria("uploadFile");
		DetachedCriteria formatDefinitionCriteria = uploadFileCriteria.createCriteria("formatDefinition");
		formatDefinitionCriteria.add(Restrictions.idEq(formatDefinitionPk));

		return countByDetachedCriteria(criteria);
	}

	/* (non-Javadoc)
	 * @see com.feib.soeasy.dao.PaymentDao#queryPaginationByPayment(com.feib.soeasy.model.Payment, com.feib.soeasy.model.FormatDefinition, java.util.Date, java.util.Date, java.util.Date, java.util.Date, com.feib.soeasy.pagination.PaginatedList)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public PaginatedList<Payment> queryPaginationByPayment(Payment payment, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo, PaginatedList<Payment> paginationConfig) {

		PaginatedList<Payment> result = new PaginatedList<Payment>();

		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		result.setFullListSize(super.countByDetachedCriteria(
				createDetachedCriteriaByUploadFile(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo)));


		// 筆數大於0
		if (0 < result.getFullListSize())
		{
			DetachedCriteria criteria = 
				createDetachedCriteriaByUploadFile(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo);

			//排序
			if (null != paginationConfig.getSortCriterion())
			{
				// 由小到大
				if (PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection())
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
				else // 由大到小
				{
					criteria.addOrder(Order.asc(paginationConfig.getSortCriterion()));
				}
			}


			int maxResults = result.getPageSize(); //the maximum number of results
			int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			result.setPaginatedList(this.getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults));
		}

		return result;

	}

	/**
	 * @param payment
	 * @param formatDefinition
	 * @param payDueFrom
	 * @param payDueTo
	 * @param writeOffDateFrom
	 * @param writeOffDateTo
	 * @return
	 */
	private DetachedCriteria createDetachedCriteriaByUploadFile(Payment entity, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo) {

		DetachedCriteria criteria = DetachedCriteria.forClass(this.getPersistentClass());
		criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);


		// 收款類別
		if (null != formatDefinition)
		{
			DetachedCriteria uploadFileCriteria = criteria.createCriteria("uploadFile");
			DetachedCriteria formatDefinitionCriteria = uploadFileCriteria.createCriteria("formatDefinition");
			if (null != formatDefinition.getFormatDefinitionPk())
			{
				formatDefinitionCriteria.add(Restrictions.idEq(formatDefinition.getFormatDefinitionPk()));
			}
			else
			{
				Example e = Example.create(formatDefinition);
				e.enableLike(MatchMode.START);
				e.excludeZeroes();
				formatDefinitionCriteria.add(e);
			}

		}


		// 屬於哪一個群組
		if (null != entity.getGroup())
		{
			DetachedCriteria detachedCriteria = criteria.createCriteria("group");

			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk())
			{
				detachedCriteria.add(Restrictions.idEq(entity.getGroup().getGroupPk()));
			}

			// Group 其他查詢條件
			Example e = Example.create(entity.getGroup());
			e.enableLike(MatchMode.START);
			e.excludeZeroes();
			detachedCriteria.add(e);
		}

		// 繳費截止日期
		// 有起迄
		if (null != payDueFrom && null != payDueTo)
		{
			criteria.add(Restrictions.between("payDue",payDueFrom, payDueTo));
		}
		else if (null != payDueFrom) // 只有起
		{
			criteria.add(Restrictions.ge("payDue", payDueFrom));
		}
		else if (null != payDueTo) // 只有迄
		{
			criteria.add(Restrictions.le("payDue", payDueTo));
		}
		else if (null != entity.getPayDue()) //單一日期
		{
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			criteria.add(Restrictions.between("payDue", from, to));
		}


		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo)
		{
			DetachedCriteria detachedCriteria = criteria.createCriteria("writeOffPayment");            

			// 銷帳日期
			// 有起迄
			if (null != writeOffDateFrom && null != writeOffDateTo)
			{
				detachedCriteria.add(Restrictions.between("writeOffDate",writeOffDateFrom, writeOffDateTo));
				detachedCriteria.add(Restrictions.eq("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK));
			}
			else if (null != writeOffDateFrom) // 只有起
			{
				detachedCriteria.add(Restrictions.ge("writeOffDate", writeOffDateFrom));
				detachedCriteria.add(Restrictions.eq("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK)); //正常銷帳
			}
			else if (null != writeOffDateTo) // 只有迄
			{
				detachedCriteria.add(Restrictions.le("writeOffDate", writeOffDateTo));
				detachedCriteria.add(Restrictions.eq("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK)); //正常銷帳
			}

			// 實際繳款日期
			// 有起迄
			if (null != txDateFrom && null != txDateTo)
			{
				detachedCriteria.add(Restrictions.between("txDate",txDateFrom, txDateTo));
				detachedCriteria.add(Restrictions.eq("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK)); //正常銷帳
			}
			else if (null != txDateFrom) // 只有起
			{
				detachedCriteria.add(Restrictions.ge("txDate", txDateFrom));
				detachedCriteria.add(Restrictions.eq("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK)); //正常銷帳
			}
			else if (null != txDateTo) // 只有迄
			{
				detachedCriteria.add(Restrictions.le("txDate", txDateTo));
				detachedCriteria.add(Restrictions.eq("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK)); //正常銷帳
			}
		}

		//        //判斷paymentCanceled是否有對應的資料
		//        if(null != entity.getPaymentCanceled())
		//        {
		//            if(null != entity.getPaymentCanceled().getPaymentCanceledPk())
		//            {
		//                //以PaymentCanceled查詢
		//                criteria.add(Restrictions.eq("paymentCanceled", entity.getPaymentCanceled()));
		//            }
		//            else
		//            {
		//                criteria.add(Restrictions.isNull("paymentCanceled"));
		//            }
		//        }
		//
		//        //判斷writeOffPayment是否有對應的資料
		//        if(null != entity.getWriteOffPayment())
		//        {
		//            if(null != entity.getWriteOffPayment().getWriteOffPaymentPk())
		//            {
		//                //以writeOffPayment查詢
		//                criteria.add(Restrictions.eq("writeOffPayment", entity.getWriteOffPayment()));
		//            }
		//            else
		//            {
		//                criteria.add(Restrictions.isNull("writeOffPayment"));
		//            }
		//        }


		// 繳費狀態，這裡多了一個未繳款狀態
		if (null != entity.getPaymentStatus())
		{
			if (1 == entity.getPaymentStatus().intValue())
			{
				// 未放行資料
				criteria.add(Restrictions.eq("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM));
			}
			else if (2 == entity.getPaymentStatus().intValue())
			{
				// 待繳款，這裡的代繳款是指 未繳且未超過繳費期限
				criteria.add(Restrictions.eq("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING));

				// 排除逾期
				Date from = DateUtil.getBeginOfDateTime(new Date());
				criteria.add(Restrictions.ge("payDue", from));
			}
			else if (3 == entity.getPaymentStatus().intValue())
			{
				// 註銷中
				criteria.add(Restrictions.eq("paymentStatus", Payment.PAYMENT_STATUS_IN_CANCELING));
			}
			else if (4 == entity.getPaymentStatus().intValue())
			{
				// 已註銷
				criteria.add(Restrictions.eq("paymentStatus", Payment.PAYMENT_STATUS_CANCELED));
			}
			else if (5 == entity.getPaymentStatus().intValue())
			{
				// 已繳款
				criteria.add(Restrictions.eq("paymentStatus", Payment.PAYMENT_STATUS_CLOSED));
			}
			else if (6 == entity.getPaymentStatus().intValue())
			{
				// 逾期 = 未繳款 且 超過繳費期限
				criteria.add(Restrictions.eq("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING));

				Date to = DateUtil.getEndOfDateTime(new Date());
				criteria.add(Restrictions.le("payDue", to));
			}

		}
		else
		{
			// 排除未放行資料
			criteria.add(Restrictions.ne("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM));
		}

		// 客戶姓名
		if (StringUtils.isNotEmpty(entity.getPayerName()))
		{
			criteria.add(Restrictions.like("payerName",entity.getPayerName(), MatchMode.START));
		}

		// 客戶代號
		if (StringUtils.isNotEmpty(entity.getPayerId()))
		{
			criteria.add(Restrictions.like("payerId",entity.getPayerId(), MatchMode.START));
		}

		// 繳款帳號
		if (StringUtils.isNotEmpty(entity.getVirtualAccount()))
		{
			criteria.add(Restrictions.like("virtualAccount",entity.getVirtualAccount(), MatchMode.START));

		}
		if(null != entity.getStudentId())
		{
			criteria.add(Restrictions.eq("studentId", entity.getStudentId()));
		}



		return criteria;
	}

	/* (non-Javadoc)
	 * @see com.feib.soeasy.dao.PaymentDao#queryPaginationByPaymentHql(com.feib.soeasy.model.Payment, com.feib.soeasy.model.FormatDefinition, java.util.Date, java.util.Date, java.util.Date, java.util.Date, com.feib.soeasy.pagination.PaginatedList)
	 */
	@SuppressWarnings("unchecked")
	public PaginatedList<Payment> queryPaginationByPaymentSql(Payment payment, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo, PaginatedList<Payment> paginationConfig) {


		PaginatedList<Payment> result = new PaginatedList<Payment>();

		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		result.setFullListSize(
				queryPaymentByUploadFile(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo).list().size()
		);


		// 筆數大於0
		if (0 < result.getFullListSize())
		{
			int maxResults = result.getPageSize(); //the maximum number of results
			int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			List<?> list = queryPaymentByUploadFileSql(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo, paginationConfig)
			.setFirstResult(firstResult)
			.setMaxResults(maxResults)
			.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
			.list(); 

			/* 手動把查詢結果轉化為payment對象 */
			List<Payment> tempList = new ArrayList<Payment>();
			for(int i = 0 ; i < list.size() ; i++) {
				/* 轉化為MAP */
				Map map = (Map)list.get(i);
				/* 新建繳費單對象 */
				Payment tempPayment = new Payment();
				/* 繳費單Pk */
				String tempPaymentPk = map.get("payment_pk") == null ? null : map.get("payment_pk").toString();
				if(StringUtils.isNotEmpty(tempPaymentPk)){
					try {
						Long paymentPk = Long.valueOf(tempPaymentPk);
						tempPayment.setPaymentPk(paymentPk);
					} catch (Exception e) {
						logger.debug("轉化paymentPk時出錯: " + e);
					}
				}
				/* 繳費單狀態 */
				String tempPaymentStatus = map.get("payment_status") == null ? null : map.get("payment_status").toString();
				if(StringUtils.isNotEmpty(tempPaymentStatus)){
					try {
						Integer paymentStatus = Integer.valueOf(tempPaymentStatus);
						tempPayment.setPaymentStatus(paymentStatus);
					} catch (Exception e) {
						logger.debug("轉化paymentStatus時出錯: " + e);
					}
				}
				/* 繳費人MAIL */
				String tempPayerEmail = map.get("payer_email") == null ? null : map.get("payer_email").toString();
				if(StringUtils.isNotEmpty(tempPayerEmail)){
					tempPayment.setPayerEmail(tempPayerEmail);
				}
				/* 虛擬帳號 */
				String tempVirtualAccount = map.get("virtual_account") == null ? null : map.get("virtual_account").toString();
				if(StringUtils.isNotEmpty(tempVirtualAccount)){
					tempPayment.setVirtualAccount(tempVirtualAccount);
				}
				/* 繳費人名稱 */
				String tempPayerName = map.get("payer_name") == null ? null : map.get("payer_name").toString();
				if(StringUtils.isNotEmpty(tempPayerName)){
					tempPayment.setPayerName(tempPayerName);
				}
				/* 學生ID */
				String tempStudentId = map.get("student_id") == null ? null : map.get("student_id").toString();
				if(StringUtils.isNotEmpty(tempStudentId)){
					tempPayment.setStudentId(tempStudentId);
				}
				/* 繳費人ID */
				String tempPayerId = map.get("payer_id") == null ? null : map.get("payer_id").toString();
				if(StringUtils.isNotEmpty(tempPayerId)){
					tempPayment.setPayerId(tempPayerId);
				}
				/* 繳費期限 */
				String tempPayDue = map.get("pay_due") == null ? null : map.get("pay_due").toString();
				if(StringUtils.isNotEmpty(tempPayDue)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date payDue;
					try {
						payDue = df.parse(tempPayDue);
						tempPayment.setPayDue(payDue);
					} catch (ParseException e) {
						logger.debug("轉化PayDue時發生錯誤: " + e);
					}  
				}
				/* 總計金額 */
				String tempTotalAmount = map.get("total_amount") == null ? null : map.get("total_amount").toString();
				if(StringUtils.isNotEmpty(tempTotalAmount)){
					try {
						Long totalAmount = Long.valueOf(tempTotalAmount);
						tempPayment.setTotalAmount(totalAmount);
					} catch (Exception e) {
						logger.debug("轉化totalAmount時出錯: " + e);
					}
				}

				/* 銷帳記錄 */
				WriteOffPayment tempWriteOffPayment = new WriteOffPayment();
				/* 繳款日期時間 */
				String tempTxDate = map.get("tx_date") == null ? null : map.get("tx_date").toString();
				if(StringUtils.isNotEmpty(tempTxDate)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date txDate;
					try {
						txDate = df.parse(tempTxDate);
						tempWriteOffPayment.setTxDate(txDate);
					} catch (ParseException e) {
						logger.debug("轉化txDate時發生錯誤: " + e);
					}  
				}
				/* 銷帳日期時間 */
				String tempWriteOffDate = map.get("write_off_date") == null ? null : map.get("write_off_date").toString();
				if(StringUtils.isNotEmpty(tempWriteOffDate)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date writeOffDate;
					try {
						writeOffDate = df.parse(tempWriteOffDate);
						tempWriteOffPayment.setWriteOffDate(writeOffDate);
					} catch (ParseException e) {
						logger.debug("轉化writeOffDate時發生錯誤: " + e);
					}  
				}
				/* 繳費金額 */
				String tempAmount = map.get("amount") == null ? null : map.get("amount").toString();
				if(StringUtils.isNotEmpty(tempAmount)){
					try {
						Long amount = Long.valueOf(tempAmount);
						tempWriteOffPayment.setAmount(amount);
					} catch (Exception e) {
						logger.debug("轉化amount時發生錯誤: " + e);
					}
				}
				/* 繳費通路 */
				String tempChannel = map.get("channel") == null ? null : map.get("channel").toString();
				if(StringUtils.isNotEmpty(tempChannel)){
					tempWriteOffPayment.setChannel(tempChannel);
				}
				/* 核銷記錄加入繳費單對象中 */
				tempPayment.setWriteOffPayment(tempWriteOffPayment);

				/* 註銷記錄 */
				PaymentCanceled tempPaymentCanceled = new PaymentCanceled();
				/* 註銷時間 */
				String tempCancelDate = map.get("cancel_date") == null ? null : map.get("cancel_date").toString();
				if(StringUtils.isNotEmpty(tempCancelDate)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date CancelDate;
					try {
						CancelDate = df.parse(tempCancelDate);
						tempPaymentCanceled.setCancelDate(CancelDate);
					} catch (ParseException e) {
						logger.debug("轉化CancelDate時發生錯誤: " + e);
					}  
				}
				/* 註銷記錄加入繳費單對象中 */
				tempPayment.setPaymentCanceled(tempPaymentCanceled);

				/* 加入LIST中 */
				tempList.add(tempPayment);
			}

			result.setPaginatedList(tempList);
		}

		return result;
	}

	public Query queryPaymentByUploadFile(Payment entity, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo)
	{
		StringBuffer sql = new StringBuffer();

		sql.append("select distinct p.payment_pk ");
		/* 繳費單表 */
		sql.append("from payment p ");
		/* 所屬繳費匯入檔 */
		sql.append("left join upload_file uf ");
		sql.append("on p.upload_file_pk = uf.upload_file_pk ");
		/* 所屬帳單業者 */
		sql.append("left join app_group ag ");
		sql.append("on p.group_pk = ag.group_pk ");
		/* 擴充欄位 */
		sql.append("left join extra_columns ec ");
		sql.append("on p.extra_columns_pk = ec.extra_columns_pk ");
		/* 銷帳資料 */
		sql.append("left join write_off_payment wof ");
		sql.append("on p.write_off_payment_pk = wof.write_off_payment_pk ");
		sql.append("left join payment_canceled pc ");
		sql.append("on p.payment_Canceled_Pk = pc.payment_canceled_pk ");
		/* 條件 */
		sql.append("where 1=1 ");

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				sql.append("and uf.format_def_pk = :format_def_pk ");
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and ag.group_pk = :group_pk ");
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}
		/* 只有起 */
		else if (null != payDueFrom){
			sql.append("and p.pay_Due >= :payDueFrom ");
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			sql.append("and p.pay_Due <= :payDueTo ");
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date between :writeOffDateFrom and :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date between :paymentCancelDateFrom and :paymentCancelDateTo ) ");
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				sql.append("and (wof.write_Off_Date >= :writeOffDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date >= :paymentCancelDateFrom ) ");
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date <= :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date <= :paymentCancelDateTo ) ");
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				sql.append("and wof.tx_Date between :txDateFrom and :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有起 */
			else if (null != txDateFrom){
				sql.append("and wof.tx_Date >= :txDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有迄 */
			else if (null != txDateTo){
				sql.append("and wof.tx_Date <= :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
		}

		/* 繳費狀態，這裡多了一個未繳款狀態 */
		if (null != entity.getPaymentStatus()){
			if(1 == entity.getPaymentStatus().intValue()){
				/* 未放行資料 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(2 == entity.getPaymentStatus().intValue()){
				/* 代繳款,這裡的代繳款是指 未繳且未超過繳費期限 */
				sql.append("and p.payment_Status = :paymentStatus ");

				/* 排除逾期 */
				sql.append("and p.pay_Due >= :beginOfDateTime ");
			}else if(3 == entity.getPaymentStatus().intValue()){
				/* 註銷中 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(4 == entity.getPaymentStatus().intValue()){
				/* 已註銷 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(5 == entity.getPaymentStatus().intValue()){
				/* 已繳款 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(6 == entity.getPaymentStatus().intValue()){
				/* 逾期 = 未繳款 且 超過繳費期限 */
				sql.append("and p.payment_Status = :paymentStatus ");

				sql.append("and p.pay_Due < :endOfDateTime ");
			}
		}else{
			/* 排除未放行資料 */
			sql.append("and p.payment_Status <> :paymentStatus ");
		}

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			sql.append("and p.payer_Name like :payerName ");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			sql.append("and p.payer_Id like :payerId ");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			sql.append("and p.virtual_Account like :virtualAccount ");
		}

		if(null != entity.getStudentId()){
			sql.append("and p.student_Id like :studentId ");
		}

		Query query = getSession().createSQLQuery(sql.toString());

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				query.setParameter("format_def_pk", formatDefinition.getFormatDefinitionPk());
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				query.setParameter("group_pk", entity.getGroup().getGroupPk());
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			query.setParameter("payDueFrom", payDueFrom);
			query.setParameter("payDueTo", payDueTo);
		}
		/* 只有起 */
		else if (null != payDueFrom){
			query.setParameter("payDueFrom", payDueFrom);
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			query.setParameter("payDueTo", payDueTo);
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			query.setParameter("payDueFrom", from);
			query.setParameter("payDueTo", to);
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有起 */
			else if (null != txDateFrom){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有迄 */
			else if (null != txDateTo){
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
		}

		/* 繳費狀態，這裡多了一個未繳款狀態 */
		if (null != entity.getPaymentStatus()){
			if(1 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM);
			}else if(2 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);

				Date from = DateUtil.getBeginOfDateTime(new Date());
				query.setParameter("beginOfDateTime", from);
			}else if(3 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_CANCELING);
			}else if(4 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CANCELED);
			}else if(5 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CLOSED);
			}else if(6 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);

				Date to = DateUtil.getBeginOfDateTime(new Date());        		
				query.setParameter("endOfDateTime", to);
			}
		}else{
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM);
		}

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			query.setParameter("payerName", "%" + entity.getPayerName() + "%");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			query.setParameter("payerId", "%" + entity.getPayerId() + "%");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			query.setParameter("virtualAccount", "%" + entity.getVirtualAccount() + "%");
		}

		if(null != entity.getStudentId()){
			query.setParameter("studentId", "%" + entity.getStudentId()+ "%");
		}

		return query;
	}

	public Query queryPaymentByUploadFileSql(Payment entity, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo,PaginatedList<Payment> paginationConfig)
	{
		StringBuffer sql = new StringBuffer();

		sql.append("select distinct p.payment_status, p.payer_email, p.payment_pk, p.virtual_account, p.payer_name, p.student_id, ");
		sql.append("p.payer_id, p.pay_due, p.total_amount, wof.tx_date, wof.write_off_date, wof.amount, wof.channel, ");
		sql.append("pc.cancel_date ");
		/* 繳費單表 */
		sql.append("from payment p ");
		/* 所屬繳費匯入檔 */
		sql.append("left join upload_file uf ");
		sql.append("on p.upload_file_pk = uf.upload_file_pk ");
		/* 所屬帳單業者 */
		sql.append("left join app_group ag ");
		sql.append("on p.group_pk = ag.group_pk ");
		/* 擴充欄位 */
		sql.append("left join extra_columns ec ");
		sql.append("on p.extra_columns_pk = ec.extra_columns_pk ");
		/* 銷帳資料 */
		sql.append("left join write_off_payment wof ");
		sql.append("on p.write_off_payment_pk = wof.write_off_payment_pk ");
		sql.append("left join payment_canceled pc ");
		sql.append("on p.payment_Canceled_Pk = pc.payment_canceled_pk ");
		/* 條件 */
		sql.append("where 1=1 ");

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				sql.append("and uf.format_def_pk = :format_def_pk ");
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and ag.group_pk = :group_pk ");
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}
		/* 只有起 */
		else if (null != payDueFrom){
			sql.append("and p.pay_Due >= :payDueFrom ");
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			sql.append("and p.pay_Due <= :payDueTo ");
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date between :writeOffDateFrom and :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date between :paymentCancelDateFrom and :paymentCancelDateTo ) ");
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				sql.append("and (wof.write_Off_Date >= :writeOffDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date >= :paymentCancelDateFrom ) ");
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date <= :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date <= :paymentCancelDateTo ) ");
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				sql.append("and wof.tx_Date between :txDateFrom and :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有起 */
			else if (null != txDateFrom){
				sql.append("and wof.tx_Date >= :txDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有迄 */
			else if (null != txDateTo){
				sql.append("and wof.tx_Date <= :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
		}

		/* 繳費狀態，這裡多了一個未繳款狀態 */
		if (null != entity.getPaymentStatus()){
			if(1 == entity.getPaymentStatus().intValue()){
				/* 未放行資料 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(2 == entity.getPaymentStatus().intValue()){
				/* 代繳款,這裡的代繳款是指 未繳且未超過繳費期限 */
				sql.append("and p.payment_Status = :paymentStatus ");

				/* 排除逾期 */
				sql.append("and p.pay_Due >= :beginOfDateTime ");
			}else if(3 == entity.getPaymentStatus().intValue()){
				/* 註銷中 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(4 == entity.getPaymentStatus().intValue()){
				/* 已註銷 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(5 == entity.getPaymentStatus().intValue()){
				/* 已繳款 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(6 == entity.getPaymentStatus().intValue()){
				/* 逾期 = 未繳款 且 超過繳費期限 */
				sql.append("and p.payment_Status = :paymentStatus ");

				sql.append("and p.pay_Due < :endOfDateTime ");
			}
		}else{
			/* 排除未放行資料 */
			sql.append("and p.payment_Status <> :paymentStatus ");
		}

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			sql.append("and p.payer_Name like :payerName ");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			sql.append("and p.payer_Id like :payerId ");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			sql.append("and p.virtual_Account like :virtualAccount ");
		}

		if(null != entity.getStudentId()){
			sql.append("and p.student_Id like :studentId ");
		}

		/* 排序 */
		if(null != paginationConfig && null != paginationConfig.getSortCriterion() ){
			/* 由小到大 */
			if(PaginatedList.SORT_DIRECTION_ASC == paginationConfig.getSortDirection()){
				sql.append(" order by " + paginationConfig.getSortCriterion() + " asc ");
			}
			/* 由大到小 */
			else{
				sql.append(" order by " + paginationConfig.getSortCriterion() + " desc ");
			}
		}

		Query query = getSession().createSQLQuery(sql.toString());

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				query.setParameter("format_def_pk", formatDefinition.getFormatDefinitionPk());
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				query.setParameter("group_pk", entity.getGroup().getGroupPk());
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			query.setParameter("payDueFrom", payDueFrom);
			query.setParameter("payDueTo", payDueTo);
		}
		/* 只有起 */
		else if (null != payDueFrom){
			query.setParameter("payDueFrom", payDueFrom);
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			query.setParameter("payDueTo", payDueTo);
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			query.setParameter("payDueFrom", from);
			query.setParameter("payDueTo", to);
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有起 */
			else if (null != txDateFrom){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有迄 */
			else if (null != txDateTo){
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
		}

		/* 繳費狀態，這裡多了一個未繳款狀態 */
		if (null != entity.getPaymentStatus()){
			if(1 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM);
			}else if(2 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);

				Date from = DateUtil.getBeginOfDateTime(new Date());
				query.setParameter("beginOfDateTime", from);
			}else if(3 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_CANCELING);
			}else if(4 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CANCELED);
			}else if(5 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CLOSED);
			}else if(6 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);

				Date to = DateUtil.getBeginOfDateTime(new Date());        		
				query.setParameter("endOfDateTime", to);
			}
		}else{
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM);
		}

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			query.setParameter("payerName", "%" + entity.getPayerName() + "%");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			query.setParameter("payerId", "%" + entity.getPayerId() + "%");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			query.setParameter("virtualAccount", "%" + entity.getVirtualAccount() + "%");
		}

		if(null != entity.getStudentId()){
			query.setParameter("studentId", "%" +entity.getStudentId()+ "%");
		}

		return query;
	}

	/* (non-Javadoc)
	 * @see com.feib.soeasy.dao.PaymentDao#queryPaymentByVirtualAccount(java.lang.String, boolean)
	 */
	@Override
	public Payment queryPaymentByVirtualAccount(String virtualAccount, boolean unPay) {
		StringBuffer hql = new StringBuffer();
		hql.append("from Payment payment ");
		hql.append("where payment.virtualAccount = :virtualAccount ");
		if (unPay)
		{
			hql.append("and payment.paymentStatus = :paymentStatus ");
		}
		else
		{
			hql.append("and payment.paymentStatus = :paymentStatus ");
		}

		Query query = getSession().createQuery(hql.toString());
		query.setParameter("virtualAccount", virtualAccount);
		if (unPay)
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);
		else
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CLOSED);

		@SuppressWarnings("unchecked")
		List<Payment> list = query.list();
		if (null == list || 0 == list.size())
			return null;
		return list.get(0);
	}
	//黃郁珊 依中租需求修改
	public List<String> queryPayerIdByUploadFilePk(Long uploadFilePk)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("select distinct payment.payerId from Payment payment ");
		hql.append("where payment.uploadFile = ");
		hql.append(String.valueOf(uploadFilePk));

		List<String> list = getSession().createQuery(hql.toString()).list();

		return list;
	}

	public List<String> queryPaymentStatusByUploadFilePk(Long uploadFilePk)
	{
		StringBuffer hql = new StringBuffer();
		hql.append("select distinct payment.paymentStatus from Payment payment ");
		hql.append("where payment.uploadFile = ");
		hql.append(String.valueOf(uploadFilePk))
		.append(" AND payment.paymentStatus <>2 ");
		List<String> list = getSession().createQuery(hql.toString()).list();

		return list;
	}

	public List<Payment> queryPayementByPayerId(String payerId, Long uploadFilePk)
	{
		Criteria criteria = getSession().createCriteria(Payment.class);
		criteria.add(Restrictions.eq("payerId", payerId));
		UploadFile uploadFile = new UploadFile();
		uploadFile.setUploadFilePk(uploadFilePk);
		criteria.add(Restrictions.eq("uploadFile", uploadFile));
		criteria.addOrder(Order.asc("virtualAccount"));

		return criteria.list();
	}
	public int getPaymentByGroup_type(String virtualAccount)
	{

		StringBuffer hql = new StringBuffer();
		hql.append("select APP_GROUP.GROUP_NO, APP_GROUP.GROUP_NAME, PAYMENT.GROUP_PK, ");
		hql.append("PAYMENT.VIRTUAL_ACCOUNT,APP_GROUP.GROUP_TYPE ");
		hql.append("from APP_GROUP,PAYMENT ");      
		hql.append("where APP_GROUP.GROUP_PK = PAYMENT.GROUP_PK and PAYMENT.VIRTUAL_ACCOUNT ='");
		hql.append(virtualAccount).append("' and APP_GROUP.GROUP_TYPE ='4'");

		List list = getSession().createSQLQuery(hql.toString()).list();

		return list.size();

	}



	@Override
	public List<Payment> queryDownloadPayments(Payment entity,
			Date qryPayDueFrom, Date qryPayDueTo, String qryVirtualAccountFrom,
			String qryVirtualAccountTo) {

		DetachedCriteria criteria = DetachedCriteria.forClass(Payment.class);
		criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
		/* 屬於哪一個群組 */
		// 屬於哪一個群組
		if (null != entity.getGroup())
		{
			DetachedCriteria detachedCriteria = criteria.createCriteria("group");

			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk())
			{
				detachedCriteria.add(Restrictions.idEq(entity.getGroup().getGroupPk()));
			}

			// Group 其他查詢條件
			Example e = Example.create(entity.getGroup());
			e.enableLike(MatchMode.START);
			e.excludeZeroes();
			detachedCriteria.add(e);
		}
		if(null !=qryPayDueFrom && null !=qryPayDueTo)
			criteria.add(Restrictions.between("payDue", qryPayDueFrom, qryPayDueTo));
		
		if(null !=qryVirtualAccountFrom && null !=qryVirtualAccountTo)
			criteria.add(Restrictions.between("virtualAccount", qryVirtualAccountFrom, qryVirtualAccountTo));

		if(null !=entity.getPayerId())
			criteria.add(Restrictions.eq("payerId", entity.getPayerId()));
		else if(null !=entity.getStudentId())
			criteria.add(Restrictions.eq("studentId", entity.getStudentId()));

		/*  排除未放行資料*/
		criteria.add(Restrictions.ne("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM));

		return this.getHibernateTemplate().findByCriteria(criteria); 

	}


	@Override
	public List<Map<String,Object>> querySumByPaymentSql(Payment entity,
			FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo,
			Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom,
			Date txDateTo) {
		StringBuffer sql = new StringBuffer();

		sql.append("select count(*) as  count, sum(a.total_amount) as total_amount,  a.payment_Status as payment_Status ");
		sql.append("from ( ");		
		sql.append("select ");
		sql.append("(CASE   ");
		sql.append(" WHEN   TOTAL_LOANABLE_AMOUNT = wof.AMOUNT   THEN TOTAL_LOANABLE_AMOUNT ");
		sql.append(" else p.total_amount ");
		sql.append(" END ");
		sql.append(") as total_amount ,");
		sql.append("( ");	
		sql.append("CASE    p.payment_Status ");
		sql.append("WHEN 2 ");
		sql.append("THEN ");
		sql.append("(case ");		
		sql.append("WHEN   convert(varchar,  p.pay_Due,112 )<CONVERT(varchar, getdate(), 112 )  ");	
		sql.append("THEN 6 else p.payment_Status ");
		sql.append("END ) ");		
		sql.append("else ");			
		sql.append("p.payment_Status ");		                   
		sql.append("END ");
		sql.append(") as payment_Status ");			

		/* 繳費單表 */
		sql.append("from payment p ");
		/* 所屬繳費匯入檔 */
		sql.append("left join upload_file uf ");
		sql.append("on p.upload_file_pk = uf.upload_file_pk ");
		/* 所屬帳單業者 */
		sql.append("left join app_group ag ");
		sql.append("on p.group_pk = ag.group_pk ");
		/* 擴充欄位 */
		sql.append("left join extra_columns ec ");
		sql.append("on p.extra_columns_pk = ec.extra_columns_pk ");
		/* 銷帳資料 */
		sql.append("left join write_off_payment wof ");
		sql.append("on p.write_off_payment_pk = wof.write_off_payment_pk ");
		sql.append("left join payment_canceled pc ");
		sql.append("on p.payment_Canceled_Pk = pc.payment_canceled_pk ");
		/* 條件 */
		sql.append("where 1=1 ");

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				sql.append("and uf.format_def_pk = :format_def_pk ");
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and ag.group_pk = :group_pk ");
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}
		/* 只有起 */
		else if (null != payDueFrom){
			sql.append("and p.pay_Due >= :payDueFrom ");
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			sql.append("and p.pay_Due <= :payDueTo ");
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date between :writeOffDateFrom and :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date between :paymentCancelDateFrom and :paymentCancelDateTo ) ");
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				sql.append("and (wof.write_Off_Date >= :writeOffDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date >= :paymentCancelDateFrom ) ");
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date <= :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date <= :paymentCancelDateTo ) ");
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				sql.append("and wof.tx_Date between :txDateFrom and :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有起 */
			else if (null != txDateFrom){
				sql.append("and wof.tx_Date >= :txDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有迄 */
			else if (null != txDateTo){
				sql.append("and wof.tx_Date <= :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
		}

		/* 繳費狀態，這裡多了一個未繳款狀態 */
		if (null != entity.getPaymentStatus()){
			if(1 == entity.getPaymentStatus().intValue()){
				/* 未放行資料 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(2 == entity.getPaymentStatus().intValue()){
				/* 代繳款,這裡的代繳款是指 未繳且未超過繳費期限 */
				sql.append("and p.payment_Status = :paymentStatus ");

				/* 排除逾期 */
				sql.append("and p.pay_Due >= :beginOfDateTime ");
			}else if(3 == entity.getPaymentStatus().intValue()){
				/* 註銷中 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(4 == entity.getPaymentStatus().intValue()){
				/* 已註銷 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(5 == entity.getPaymentStatus().intValue()){
				/* 已繳款 */
				sql.append("and p.payment_Status = :paymentStatus ");
			}else if(6 == entity.getPaymentStatus().intValue()){
				/* 逾期 = 未繳款 且 超過繳費期限 */
				sql.append("and p.payment_Status = :paymentStatus ");

				sql.append("and p.pay_Due < :endOfDateTime ");
			}
		}else{
			/* 排除未放行資料 */
			sql.append("and p.payment_Status <> :paymentStatus ");
		}

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			sql.append("and p.payer_Name like :payerName ");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			sql.append("and p.payer_Id like :payerId ");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			sql.append("and p.virtual_Account like :virtualAccount ");
		}

		if(null != entity.getStudentId()){
			sql.append("and p.student_Id like :studentId ");
		}
		sql.append(" ) a ");
		sql.append(" group by  payment_Status ");


		Query query = getSession().createSQLQuery(sql.toString());

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				query.setParameter("format_def_pk", formatDefinition.getFormatDefinitionPk());
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				query.setParameter("group_pk", entity.getGroup().getGroupPk());
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			query.setParameter("payDueFrom", payDueFrom);
			query.setParameter("payDueTo", payDueTo);
		}
		/* 只有起 */
		else if (null != payDueFrom){
			query.setParameter("payDueFrom", payDueFrom);
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			query.setParameter("payDueTo", payDueTo);
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			query.setParameter("payDueFrom", from);
			query.setParameter("payDueTo", to);
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有起 */
			else if (null != txDateFrom){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有迄 */
			else if (null != txDateTo){
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
		}

		/* 繳費狀態，這裡多了一個未繳款狀態 */
		if (null != entity.getPaymentStatus()){
			if(1 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM);
			}else if(2 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);

				Date from = DateUtil.getBeginOfDateTime(new Date());
				query.setParameter("beginOfDateTime", from);
			}else if(3 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_CANCELING);
			}else if(4 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CANCELED);
			}else if(5 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_CLOSED);
			}else if(6 == entity.getPaymentStatus().intValue()){
				query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_IN_PAYING);

				Date to = DateUtil.getBeginOfDateTime(new Date());        		
				query.setParameter("endOfDateTime", to);
			}
		}else{
			query.setParameter("paymentStatus", Payment.PAYMENT_STATUS_WAIT_CONFIRM);
		}

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			query.setParameter("payerName", "%" + entity.getPayerName() + "%");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			query.setParameter("payerId", "%" + entity.getPayerId() + "%");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			query.setParameter("virtualAccount", "%" + entity.getVirtualAccount() + "%");
		}

		if(null != entity.getStudentId()){
			query.setParameter("studentId", "%" + entity.getStudentId()+ "%");
		}
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}



	@Override
	public PaginatedList<Payment> queryDelayPaymentPaginationBySql(
			Payment payment, FormatDefinition formatDefinition,
			Date payDueFrom, Date payDueTo, Date writeOffDateFrom,
			Date writeOffDateTo, Date txDateFrom, Date txDateTo,
			PaginatedList<Payment> paginationConfig,boolean isSchool, int delayCount) {
		// TODO Auto-generated method stub
		PaginatedList<Payment> result = new PaginatedList<Payment>();

		result.setPageSize(paginationConfig.getPageSize());
		result.setPageNumber(paginationConfig.getPageNumber());
		result.setSortCriterion(paginationConfig.getSortCriterion());
		result.setSortDirection(paginationConfig.getSortDirection());

		result.setFullListSize(
				queryDelayPaymentCount(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo,isSchool,  delayCount).list().size()
		);


		// 筆數大於0
		if (0 < result.getFullListSize())
		{
			int maxResults = result.getPageSize(); //the maximum number of results
			int firstResult = result.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
			List<?> list = queryDelayPaymentSql(payment, formatDefinition, payDueFrom, payDueTo, writeOffDateFrom, writeOffDateTo, txDateFrom, txDateTo, paginationConfig,isSchool,  delayCount)
			.setFirstResult(firstResult)
			.setMaxResults(maxResults)
			.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
			.list(); 

			/* 手動把查詢結果轉化為payment對象 */
			List<Payment> tempList = new ArrayList<Payment>();
			for(int i = 0 ; i < list.size() ; i++) {
				/* 轉化為MAP */
				Map map = (Map)list.get(i);
				/* 新建繳費單對象 */
				Payment tempPayment = new Payment();
				/* 繳費單Pk */
				String tempPaymentPk = map.get("payment_pk") == null ? null : map.get("payment_pk").toString();
				if(StringUtils.isNotEmpty(tempPaymentPk)){
					try {
						Long paymentPk = Long.valueOf(tempPaymentPk);
						tempPayment.setPaymentPk(paymentPk);
					} catch (Exception e) {
						logger.debug("轉化paymentPk時出錯: " + e);
					}
				}
				/* 繳費單狀態 */
				String tempPaymentStatus = map.get("payment_status") == null ? null : map.get("payment_status").toString();
				if(StringUtils.isNotEmpty(tempPaymentStatus)){
					try {
						Integer paymentStatus = Integer.valueOf(tempPaymentStatus);
						tempPayment.setPaymentStatus(paymentStatus);
					} catch (Exception e) {
						logger.debug("轉化paymentStatus時出錯: " + e);
					}
				}
				/* 繳費人MAIL */
				String tempPayerEmail = map.get("payer_email") == null ? null : map.get("payer_email").toString();
				if(StringUtils.isNotEmpty(tempPayerEmail)){
					tempPayment.setPayerEmail(tempPayerEmail);
				}
				/* 虛擬帳號 */
				String tempVirtualAccount = map.get("virtual_account") == null ? null : map.get("virtual_account").toString();
				if(StringUtils.isNotEmpty(tempVirtualAccount)){
					tempPayment.setVirtualAccount(tempVirtualAccount);
				}
				/* 繳費人名稱 */
				String tempPayerName = map.get("payer_name") == null ? null : map.get("payer_name").toString();
				if(StringUtils.isNotEmpty(tempPayerName)){
					tempPayment.setPayerName(tempPayerName);
				}
				/* 學生ID */
				String tempStudentId = map.get("student_id") == null ? null : map.get("student_id").toString();
				if(StringUtils.isNotEmpty(tempStudentId)){
					tempPayment.setStudentId(tempStudentId);
				}
				/* 繳費人ID */
				String tempPayerId = map.get("payer_id") == null ? null : map.get("payer_id").toString();
				if(StringUtils.isNotEmpty(tempPayerId)){
					tempPayment.setPayerId(tempPayerId);
				}
				/* 繳費期限 */
				String tempPayDue = map.get("pay_due") == null ? null : map.get("pay_due").toString();
				if(StringUtils.isNotEmpty(tempPayDue)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date payDue;
					try {
						payDue = df.parse(tempPayDue);
						tempPayment.setPayDue(payDue);
					} catch (ParseException e) {
						logger.debug("轉化PayDue時發生錯誤: " + e);
					}  
				}
				/* 總計金額 */
				String tempTotalAmount = map.get("total_amount") == null ? null : map.get("total_amount").toString();
				if(StringUtils.isNotEmpty(tempTotalAmount)){
					try {
						Long totalAmount = Long.valueOf(tempTotalAmount);
						tempPayment.setTotalAmount(totalAmount);
					} catch (Exception e) {
						logger.debug("轉化totalAmount時出錯: " + e);
					}
				}

				/* 銷帳記錄 */
				WriteOffPayment tempWriteOffPayment = new WriteOffPayment();
				/* 繳款日期時間 */
				String tempTxDate = map.get("tx_date") == null ? null : map.get("tx_date").toString();
				if(StringUtils.isNotEmpty(tempTxDate)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date txDate;
					try {
						txDate = df.parse(tempTxDate);
						tempWriteOffPayment.setTxDate(txDate);
					} catch (ParseException e) {
						logger.debug("轉化txDate時發生錯誤: " + e);
					}  
				}
				/* 銷帳日期時間 */
				String tempWriteOffDate = map.get("write_off_date") == null ? null : map.get("write_off_date").toString();
				if(StringUtils.isNotEmpty(tempWriteOffDate)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date writeOffDate;
					try {
						writeOffDate = df.parse(tempWriteOffDate);
						tempWriteOffPayment.setWriteOffDate(writeOffDate);
					} catch (ParseException e) {
						logger.debug("轉化writeOffDate時發生錯誤: " + e);
					}  
				}
				/* 繳費金額 */
				String tempAmount = map.get("amount") == null ? null : map.get("amount").toString();
				if(StringUtils.isNotEmpty(tempAmount)){
					try {
						Long amount = Long.valueOf(tempAmount);
						tempWriteOffPayment.setAmount(amount);
					} catch (Exception e) {
						logger.debug("轉化amount時發生錯誤: " + e);
					}
				}
				/* 繳費通路 */
				String tempChannel = map.get("channel") == null ? null : map.get("channel").toString();
				if(StringUtils.isNotEmpty(tempChannel)){
					tempWriteOffPayment.setChannel(tempChannel);
				}
				/* 核銷記錄加入繳費單對象中 */
				tempPayment.setWriteOffPayment(tempWriteOffPayment);

				/* 註銷記錄 */
				PaymentCanceled tempPaymentCanceled = new PaymentCanceled();
				/* 註銷時間 */
				String tempCancelDate = map.get("cancel_date") == null ? null : map.get("cancel_date").toString();
				if(StringUtils.isNotEmpty(tempCancelDate)){
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
					Date CancelDate;
					try {
						CancelDate = df.parse(tempCancelDate);
						tempPaymentCanceled.setCancelDate(CancelDate);
					} catch (ParseException e) {
						logger.debug("轉化CancelDate時發生錯誤: " + e);
					}  
				}
				/* 註銷記錄加入繳費單對象中 */
				tempPayment.setPaymentCanceled(tempPaymentCanceled);

				/* 加入LIST中 */
				tempList.add(tempPayment);
			}

			result.setPaginatedList(tempList);
		}

		return result;
	}



	public Query queryDelayPaymentCount(Payment entity, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo,boolean isSchool, int delayCount)
	{
		StringBuffer sql = new StringBuffer();
		String queryId="payer_id";
		if(isSchool)
			queryId="STUDENT_ID";
		sql.append("select distinct  p.payment_pk ");
		/* 繳費單表 */
		sql.append("from payment p ");
		/* 所屬繳費匯入檔 */
		sql.append("left join upload_file uf ");
		sql.append("on p.upload_file_pk = uf.upload_file_pk ");
		/* 所屬帳單業者 */
		sql.append("left join app_group ag ");
		sql.append("on p.group_pk = ag.group_pk ");
		/* 擴充欄位 */
		sql.append("left join extra_columns ec ");
		sql.append("on p.extra_columns_pk = ec.extra_columns_pk ");
		/* 銷帳資料 */
		sql.append("left join write_off_payment wof ");
		sql.append("on p.write_off_payment_pk = wof.write_off_payment_pk ");
		sql.append("left join payment_canceled pc ");
		sql.append("on p.payment_Canceled_Pk = pc.payment_canceled_pk ");
		/* 條件 */
		sql.append(" where 1=1 ");
		if(delayCount > 0){
			sql.append(" and exists (");
		}else{
			sql.append(" and not exists (");
		}
		sql.append(" select count(").append(queryId).append(") as count   from ");
		sql.append("(");
		sql.append("select  ").append(queryId).append(",TOTAL_AMOUNT,");
		sql.append("(CASE   pp.payment_Status ");
		sql.append(" WHEN 2");
		sql.append(" THEN");
		sql.append("   	 (case   ");
		sql.append(" WHEN   convert(varchar,  pp.pay_Due,112 )<CONVERT(varchar, getdate(), 112)   THEN 6 ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END) ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END ");
		sql.append(") as payment_Status ");
		sql.append("from dbo.PAYMENT pp ");
		sql.append("where payment_Status in('2','6') ");
		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and pp.group_pk = :group_pk ");
			}
		}
		sql.append("and ").append(queryId).append(" is not null ");
		sql.append("");
		sql.append(") a ");
		sql.append("where payment_Status=6");
		sql.append("and a.").append(queryId).append("=p.").append(queryId).append(" ");
		sql.append("group by  payment_Status  ,").append(queryId).append(" ");
		sql.append("having  count(").append(queryId).append(")  > =").append(delayCount).append(" ");
		sql.append(")	 ");

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				sql.append("and uf.format_def_pk = :format_def_pk ");
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and ag.group_pk = :group_pk ");
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}
		/* 只有起 */
		else if (null != payDueFrom){
			sql.append("and p.pay_Due >= :payDueFrom ");
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			sql.append("and p.pay_Due <= :payDueTo ");
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date between :writeOffDateFrom and :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date between :paymentCancelDateFrom and :paymentCancelDateTo ) ");
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				sql.append("and (wof.write_Off_Date >= :writeOffDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date >= :paymentCancelDateFrom ) ");
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date <= :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date <= :paymentCancelDateTo ) ");
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				sql.append("and wof.tx_Date between :txDateFrom and :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有起 */
			else if (null != txDateFrom){
				sql.append("and wof.tx_Date >= :txDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有迄 */
			else if (null != txDateTo){
				sql.append("and wof.tx_Date <= :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
		}

		

		/* 忽略查詢條件的繳款狀態，只秀查詢結果為逾期2次(含)以上之客戶(包含待繳款之帳單) */
		
			/* 排除未放行資料 */
			sql.append("and p.payment_Status in (2,6) ");
		


		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			sql.append("and p.payer_Name like :payerName ");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			sql.append("and p.payer_Id like :payerId ");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			sql.append("and p.virtual_Account like :virtualAccount ");
		}

		if(null != entity.getStudentId()){
			sql.append("and p.student_Id like :studentId ");
		}


		Query query = getSession().createSQLQuery(sql.toString());

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				query.setParameter("format_def_pk", formatDefinition.getFormatDefinitionPk());
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				query.setParameter("group_pk", entity.getGroup().getGroupPk());
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			query.setParameter("payDueFrom", payDueFrom);
			query.setParameter("payDueTo", payDueTo);
		}
		/* 只有起 */
		else if (null != payDueFrom){
			query.setParameter("payDueFrom", payDueFrom);
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			query.setParameter("payDueTo", payDueTo);
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			query.setParameter("payDueFrom", from);
			query.setParameter("payDueTo", to);
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有起 */
			else if (null != txDateFrom){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有迄 */
			else if (null != txDateTo){
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
		}

		/* 忽略查詢條件的繳款狀態，只秀查詢結果為逾期2次(含)以上之客戶(包含待繳款之帳單) */ 
		
		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			query.setParameter("payerName", "%" + entity.getPayerName() + "%");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			query.setParameter("payerId", "%" + entity.getPayerId() + "%");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			query.setParameter("virtualAccount", "%" + entity.getVirtualAccount() + "%");
		}

		if(null != entity.getStudentId()){
			query.setParameter("studentId", "%" +entity.getStudentId()+ "%");
		}

		return query;
	}

	public Query queryDelayPaymentSql(Payment entity, FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo, Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom, Date txDateTo,PaginatedList<Payment> paginationConfig,boolean isSchool, int delayCount)
	{
		StringBuffer sql = new StringBuffer();
		String queryId="payer_id";
		if(isSchool)
			queryId="STUDENT_ID";
		sql.append("select distinct p.payment_status, p.payer_email, p.payment_pk, p.virtual_account, p.payer_name, p.student_id, ");
		sql.append("p.payer_id, p.pay_due, p.total_amount, wof.tx_date, wof.write_off_date, wof.amount, wof.channel, ");
		sql.append("pc.cancel_date ");
		/* 繳費單表 */
		sql.append("from payment p ");
		/* 所屬繳費匯入檔 */
		sql.append("left join upload_file uf ");
		sql.append("on p.upload_file_pk = uf.upload_file_pk ");
		/* 所屬帳單業者 */
		sql.append("left join app_group ag ");
		sql.append("on p.group_pk = ag.group_pk ");
		/* 擴充欄位 */
		sql.append("left join extra_columns ec ");
		sql.append("on p.extra_columns_pk = ec.extra_columns_pk ");
		/* 銷帳資料 */
		sql.append("left join write_off_payment wof ");
		sql.append("on p.write_off_payment_pk = wof.write_off_payment_pk ");
		sql.append("left join payment_canceled pc ");
		sql.append("on p.payment_Canceled_Pk = pc.payment_canceled_pk ");
		/* 條件 */
		sql.append(" where 1=1 ");
		if(delayCount>0){
			sql.append(" and exists (");
		}else{
			sql.append(" and not exists (");
		}
		sql.append(" select count(").append(queryId).append(") as count    from ");
		sql.append("(");
		sql.append("select  ").append(queryId).append(",TOTAL_AMOUNT,");
		sql.append("(CASE   pp.payment_Status ");
		sql.append(" WHEN 2");
		sql.append(" THEN");
		sql.append("   	 (case   ");
		sql.append(" WHEN   convert(varchar,  pp.pay_Due,112 )<CONVERT(varchar, getdate(), 112)   THEN 6 ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END) ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END ");
		sql.append(") as payment_Status ");
		sql.append("from dbo.PAYMENT pp ");
		sql.append("where payment_Status in('2','6') ");
		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and pp.group_pk = :group_pk ");
			}
		}
		sql.append("and ").append(queryId).append(" is not null ");
		sql.append("");
		sql.append(") a ");
		sql.append("where payment_Status=6");
		sql.append("and a.").append(queryId).append("=p.").append(queryId).append(" ");
		sql.append("group by  payment_Status  ,").append(queryId).append(" ");
		sql.append("having  count(").append(queryId).append(")  >= ").append(delayCount).append(" ");
		sql.append(")	 ");

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				sql.append("and uf.format_def_pk = :format_def_pk ");
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and ag.group_pk = :group_pk ");
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}
		/* 只有起 */
		else if (null != payDueFrom){
			sql.append("and p.pay_Due >= :payDueFrom ");
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			sql.append("and p.pay_Due <= :payDueTo ");
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date between :writeOffDateFrom and :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date between :paymentCancelDateFrom and :paymentCancelDateTo ) ");
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				sql.append("and (wof.write_Off_Date >= :writeOffDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date >= :paymentCancelDateFrom ) ");
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date <= :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date <= :paymentCancelDateTo ) ");
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				sql.append("and wof.tx_Date between :txDateFrom and :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有起 */
			else if (null != txDateFrom){
				sql.append("and wof.tx_Date >= :txDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有迄 */
			else if (null != txDateTo){
				sql.append("and wof.tx_Date <= :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
		}


		/* 忽略查詢條件的繳款狀態，只秀查詢結果為逾期2次(含)以上之客戶(包含待繳款之帳單) */
		
			/* 排除未放行資料 */
			sql.append("and p.payment_Status in (2,6) ");
		


		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			sql.append("and p.payer_Name like :payerName ");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			sql.append("and p.payer_Id like :payerId ");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			sql.append("and p.virtual_Account like :virtualAccount ");
		}

		if(null != entity.getStudentId()){
			sql.append("and p.student_Id like :studentId ");
		}

	
		sql.append(" order by  p.payer_Id  , p.virtual_Account  ");
			
		

		Query query = getSession().createSQLQuery(sql.toString());

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				query.setParameter("format_def_pk", formatDefinition.getFormatDefinitionPk());
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				query.setParameter("group_pk", entity.getGroup().getGroupPk());
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			query.setParameter("payDueFrom", payDueFrom);
			query.setParameter("payDueTo", payDueTo);
		}
		/* 只有起 */
		else if (null != payDueFrom){
			query.setParameter("payDueFrom", payDueFrom);
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			query.setParameter("payDueTo", payDueTo);
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			query.setParameter("payDueFrom", from);
			query.setParameter("payDueTo", to);
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有起 */
			else if (null != txDateFrom){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有迄 */
			else if (null != txDateTo){
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
		}
	

		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			query.setParameter("payerName", "%" + entity.getPayerName() + "%");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			query.setParameter("payerId", "%" + entity.getPayerId() + "%");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			query.setParameter("virtualAccount", "%" + entity.getVirtualAccount() + "%");
		}

		if(null != entity.getStudentId()){
			query.setParameter("studentId", "%" +entity.getStudentId()+ "%");
		}

		return query;
	}



	@Override
	public List<Map<String, Object>> queryDelaySumByPaymentSql(Payment entity,
			FormatDefinition formatDefinition, Date payDueFrom, Date payDueTo,
			Date writeOffDateFrom, Date writeOffDateTo, Date txDateFrom,
			Date txDateTo, boolean isSchool, int delayCount) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();

		sql.append("select count(*) as  count, sum(total_amount) as total_amount,  payment_Status ");
		sql.append("from ( ");		
		String queryId="payer_id";
		if(isSchool)
			queryId="STUDENT_ID";
		sql.append("select p.PAYMENT_PK    ");
		sql.append(",  ");		
		sql.append("(CASE   ");
		sql.append(" WHEN   TOTAL_LOANABLE_AMOUNT = wof.AMOUNT   THEN TOTAL_LOANABLE_AMOUNT ");
		sql.append(" else p.total_amount ");
		sql.append(" END ");
		sql.append(") as total_amount ,");

		sql.append("(CASE   payment_Status ");
		sql.append(" WHEN 2");
		sql.append(" THEN");
		sql.append("   	 (case   ");
		sql.append(" WHEN   convert(varchar,  pay_Due,112 )<CONVERT(varchar, getdate(), 112)   THEN 6 ");
		sql.append(" else ");
		sql.append(" payment_Status ");
		sql.append(" END) ");
		sql.append(" else ");
		sql.append(" payment_Status ");
		sql.append(" END ");
		sql.append(") as payment_Status ");
		/* 繳費單表 */
		sql.append("from payment p ");
		/* 所屬繳費匯入檔 */
		sql.append("left join upload_file uf ");
		sql.append("on p.upload_file_pk = uf.upload_file_pk ");
		/* 所屬帳單業者 */
		sql.append("left join app_group ag ");
		sql.append("on p.group_pk = ag.group_pk ");
		/* 擴充欄位 */
		sql.append("left join extra_columns ec ");
		sql.append("on p.extra_columns_pk = ec.extra_columns_pk ");
		/* 銷帳資料 */
		sql.append("left join write_off_payment wof ");
		sql.append("on p.write_off_payment_pk = wof.write_off_payment_pk ");
		sql.append("left join payment_canceled pc ");
		sql.append("on p.payment_Canceled_Pk = pc.payment_canceled_pk ");
		/* 條件 */
		sql.append(" where 1=1 ");
		if(delayCount>0){
			sql.append(" and exists (");
		}else{
			sql.append(" and not exists (");
		}
		sql.append(" select count(").append(queryId).append(") as count   from ");
		sql.append("(");
		sql.append("select  ").append(queryId).append(",TOTAL_AMOUNT,");
		sql.append("(CASE   pp.payment_Status ");
		sql.append(" WHEN 2");
		sql.append(" THEN");
		sql.append("   	 (case   ");
		sql.append(" WHEN   convert(varchar,  pp.pay_Due,112 )<CONVERT(varchar, getdate(), 112)   THEN 6 ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END) ");
		sql.append(" else ");
		sql.append(" pp.payment_Status ");
		sql.append(" END ");
		sql.append(") as payment_Status ");
		sql.append("from dbo.PAYMENT pp ");
		sql.append("where pp.payment_Status in('2','6') ");
		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and pp.group_pk = :group_pk ");
			}
		}
		sql.append("and ").append(queryId).append(" is not null ");
		sql.append("");
		sql.append(") a ");
		sql.append("where payment_Status=6");
		sql.append("and a.").append(queryId).append("=p.").append(queryId).append(" ");
		sql.append("group by  payment_Status  ,").append(queryId).append(" ");
		sql.append("having  count(").append(queryId).append(")  >= ").append(delayCount).append(" ");
		sql.append(")	 ");

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				sql.append("and uf.format_def_pk = :format_def_pk ");
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				sql.append("and ag.group_pk = :group_pk ");
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}
		/* 只有起 */
		else if (null != payDueFrom){
			sql.append("and p.pay_Due >= :payDueFrom ");
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			sql.append("and p.pay_Due <= :payDueTo ");
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			sql.append("and p.pay_Due between :payDueFrom and :payDueTo ");
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date between :writeOffDateFrom and :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date between :paymentCancelDateFrom and :paymentCancelDateTo ) ");
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				sql.append("and (wof.write_Off_Date >= :writeOffDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date >= :paymentCancelDateFrom ) ");
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				sql.append("and (wof.write_Off_Date <= :writeOffDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");

				sql.append("or pc.cancel_date <= :paymentCancelDateTo ) ");
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				sql.append("and wof.tx_Date between :txDateFrom and :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有起 */
			else if (null != txDateFrom){
				sql.append("and wof.tx_Date >= :txDateFrom ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
			/* 只有迄 */
			else if (null != txDateTo){
				sql.append("and wof.tx_Date <= :txDateTo ");
				/* 正常銷帳 */
				sql.append("and wof.write_Off_Status = :writeOffStatus ");
			}
		}

		/* 忽略查詢條件的繳款狀態，只秀查詢結果為逾期2次(含)以上之客戶(包含待繳款之帳單) */
	
			
			sql.append("and p.payment_Status in (2,6) ");
	


		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			sql.append("and p.payer_Name like :payerName ");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			sql.append("and p.payer_Id like :payerId ");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			sql.append("and p.virtual_Account like :virtualAccount ");
		}

		if(null != entity.getStudentId()){
			sql.append("and p.student_Id like :studentId ");
		}


		sql.append(" ) total ");
		sql.append(" group by  payment_Status ");


		Query query = getSession().createSQLQuery(sql.toString());

		/* 收款類別 */
		if (null != formatDefinition){
			if (null != formatDefinition.getFormatDefinitionPk()){
				query.setParameter("format_def_pk", formatDefinition.getFormatDefinitionPk());
			}
		}

		/* 屬於哪一個群組 */
		if (null != entity.getGroup()){
			// PK 有值，需加入為查詢條件
			if (null != entity.getGroup().getGroupPk()){
				query.setParameter("group_pk", entity.getGroup().getGroupPk());
			}
		}

		/* 繳費截止日期 */
		/* 有起迄 */
		if (null != payDueFrom && null != payDueTo){
			query.setParameter("payDueFrom", payDueFrom);
			query.setParameter("payDueTo", payDueTo);
		}
		/* 只有起 */
		else if (null != payDueFrom){
			query.setParameter("payDueFrom", payDueFrom);
		}
		/* 只有迄 */
		else if (null != payDueTo ){
			query.setParameter("payDueTo", payDueTo);
		}
		/* 單一日期 */
		else if (null != entity.getPayDue()){
			Date from = DateUtil.getBeginOfDateTime(entity.getPayDue());
			Date to = DateUtil.getEndOfDateTime(entity.getPayDue());
			query.setParameter("payDueFrom", from);
			query.setParameter("payDueTo", to);
		}

		/* 銷帳記錄 */
		if (null != writeOffDateFrom || null != writeOffDateTo||null != txDateFrom || null != txDateTo){
			/* 銷帳日期 */
			/* 有起迄 */
			if (null != writeOffDateFrom && null != writeOffDateTo){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}
			/* 只有起 */
			else if (null != writeOffDateFrom){
				query.setParameter("writeOffDateFrom", writeOffDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateFrom", writeOffDateFrom);
			}
			/* 只有迄 */
			else if(null != writeOffDateTo){
				query.setParameter("writeOffDateTo", writeOffDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
				query.setParameter("paymentCancelDateTo", writeOffDateTo);
			}

			/* 實際繳款日期 */
			/* 有起訖 */
			if (null != txDateFrom && null != txDateTo){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有起 */
			else if (null != txDateFrom){
				query.setParameter("txDateFrom", txDateFrom);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
			/* 只有迄 */
			else if (null != txDateTo){
				query.setParameter("txDateTo", txDateTo);
				query.setParameter("writeOffStatus", WriteOffPayment.WRITEOFF_STATUS_OK);
			}
		}

		/* 忽略查詢條件的繳款狀態，只秀查詢結果為逾期2次(含)以上之客戶(包含待繳款之帳單) */
		
		/* 客戶姓名 */
		if(StringUtils.isNotEmpty(entity.getPayerName())){
			query.setParameter("payerName", "%" + entity.getPayerName() + "%");
		}

		/* 客戶代號 */
		if(StringUtils.isNotEmpty(entity.getPayerId())){
			query.setParameter("payerId", "%" + entity.getPayerId() + "%");
		}

		/* 繳款帳號 */
		if(StringUtils.isNotEmpty(entity.getVirtualAccount())){
			query.setParameter("virtualAccount", "%" + entity.getVirtualAccount() + "%");
		}

		if(null != entity.getStudentId()){
			query.setParameter("studentId", "%" +entity.getStudentId()+ "%");
		}
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

	}

}
